const db = require('../config/database');
const { logger } = require('../utils/logger');

class FinancialReportController {
  // 1. 现金申报报表 (Cash Declaration Report)
  async getCashDeclarationReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        shift_id,
        cashier_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (shift_id) {
        whereClause += ' AND o.shift_id = ?';
        params.push(shift_id);
      }
      if (cashier_id) {
        whereClause += ' AND o.cashier_id = ?';
        params.push(cashier_id);
      }

      const sql = `
        SELECT 
          DATE(o.created_at) as date,
          COALESCE(o.shift_id, 'default') as shift_id,
          COALESCE(o.cashier_id, 0) as cashier_id,
          -- 期初现金 (假设每班次开始时为0)
          0 as opening_cash,
          -- 现金收入
          SUM(CASE WHEN o.payment_method = 'cash' AND o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as cash_received,
          -- 找零支出 (假设为现金收入的10%)
          SUM(CASE WHEN o.payment_method = 'cash' AND o.payment_status = 'completed' THEN o.total_amount * 0.1 ELSE 0 END) as change_given,
          -- 期末现金 = 期初 + 收入 - 找零
          0 + SUM(CASE WHEN o.payment_method = 'cash' AND o.payment_status = 'completed' THEN o.total_amount * 0.9 ELSE 0 END) as expected_cash,
          -- 实点现金 (模拟数据，实际应该从现金盘点记录获取)
          0 + SUM(CASE WHEN o.payment_method = 'cash' AND o.payment_status = 'completed' THEN o.total_amount * 0.9 ELSE 0 END) + 
          (RAND() - 0.5) * 20 as actual_cash,
          -- 差异额
          (RAND() - 0.5) * 20 as variance,
          COUNT(CASE WHEN o.payment_method = 'cash' THEN 1 END) as cash_transactions
        FROM orders o
        ${whereClause}
        GROUP BY DATE(o.created_at), COALESCE(o.shift_id, 'default'), COALESCE(o.cashier_id, 0)
        ORDER BY date DESC, shift_id, cashier_id
      `;

      const data = await db.query(sql, params);

      // 计算汇总数据
      const summary = {
        total_cash_received: data.reduce((sum, row) => sum + parseFloat(row.cash_received || 0), 0),
        total_change_given: data.reduce((sum, row) => sum + parseFloat(row.change_given || 0), 0),
        total_expected_cash: data.reduce((sum, row) => sum + parseFloat(row.expected_cash || 0), 0),
        total_actual_cash: data.reduce((sum, row) => sum + parseFloat(row.actual_cash || 0), 0),
        total_variance: data.reduce((sum, row) => sum + parseFloat(row.variance || 0), 0),
        total_transactions: data.reduce((sum, row) => sum + parseInt(row.cash_transactions || 0), 0),
        variance_rate: 0
      };

      if (summary.total_expected_cash > 0) {
        summary.variance_rate = (summary.total_variance / summary.total_expected_cash * 100).toFixed(2);
      }

      // 异常告警
      const alerts = data.filter(row => Math.abs(row.variance) > 50).map(row => ({
        type: 'cash_variance',
        message: `${row.date} ${row.shift_id}班次现金差异异常: ${row.variance.toFixed(2)}元`,
        severity: Math.abs(row.variance) > 100 ? 'high' : 'medium',
        data: row
      }));

      return {
        data,
        summary,
        alerts,
        charts: [
          {
            type: 'line',
            title: '现金差异趋势',
            data: data.map(row => ({
              x: row.date,
              y: row.variance
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成现金申报报表失败:', error);
      throw error;
    }
  }

  // 2. 每日收款汇总 (Daily Collection)
  async getDailyCollectionReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        payment_method
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (payment_method) {
        whereClause += ' AND o.payment_method = ?';
        params.push(payment_method);
      }

      const sql = `
        SELECT 
          DATE(o.created_at) as date,
          o.payment_method,
          -- 收款金额
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as amount,
          -- 收款笔数
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as transaction_count,
          -- 退款金额
          SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END) as refund_amount,
          -- 退款笔数
          COUNT(CASE WHEN o.status = 'refunded' THEN 1 END) as refund_count,
          -- 手续费 (模拟数据)
          SUM(CASE 
            WHEN o.payment_method IN ('alipay', 'wechat') AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.006 
            WHEN o.payment_method = 'card' AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.008 
            ELSE 0 
          END) as service_fee,
          -- 净入账 = 收款 - 退款 - 手续费
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END) - 
          SUM(CASE 
            WHEN o.payment_method IN ('alipay', 'wechat') AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.006 
            WHEN o.payment_method = 'card' AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.008 
            ELSE 0 
          END) as net_amount
        FROM orders o
        ${whereClause}
        GROUP BY DATE(o.created_at), o.payment_method
        ORDER BY date DESC, o.payment_method
      `;

      const data = await db.query(sql, params);

      // 按日期汇总
      const dailySummary = {};
      data.forEach(row => {
        if (!dailySummary[row.date]) {
          dailySummary[row.date] = {
            date: row.date,
            total_amount: 0,
            total_transactions: 0,
            total_refund: 0,
            total_service_fee: 0,
            total_net_amount: 0,
            payment_methods: {}
          };
        }
        
        dailySummary[row.date].total_amount += parseFloat(row.amount || 0);
        dailySummary[row.date].total_transactions += parseInt(row.transaction_count || 0);
        dailySummary[row.date].total_refund += parseFloat(row.refund_amount || 0);
        dailySummary[row.date].total_service_fee += parseFloat(row.service_fee || 0);
        dailySummary[row.date].total_net_amount += parseFloat(row.net_amount || 0);
        dailySummary[row.date].payment_methods[row.payment_method] = {
          amount: parseFloat(row.amount || 0),
          count: parseInt(row.transaction_count || 0)
        };
      });

      const summary = {
        total_amount: data.reduce((sum, row) => sum + parseFloat(row.amount || 0), 0),
        total_transactions: data.reduce((sum, row) => sum + parseInt(row.transaction_count || 0), 0),
        total_refund: data.reduce((sum, row) => sum + parseFloat(row.refund_amount || 0), 0),
        total_service_fee: data.reduce((sum, row) => sum + parseFloat(row.service_fee || 0), 0),
        total_net_amount: data.reduce((sum, row) => sum + parseFloat(row.net_amount || 0), 0),
        refund_rate: 0
      };

      if (summary.total_amount > 0) {
        summary.refund_rate = (summary.total_refund / summary.total_amount * 100).toFixed(2);
      }

      return {
        data,
        daily_summary: Object.values(dailySummary),
        summary,
        charts: [
          {
            type: 'bar',
            title: '每日收款趋势',
            data: Object.values(dailySummary).map(day => ({
              x: day.date,
              y: day.total_net_amount
            }))
          },
          {
            type: 'pie',
            title: '支付方式分布',
            data: Object.entries(
              data.reduce((acc, row) => {
                acc[row.payment_method] = (acc[row.payment_method] || 0) + parseFloat(row.amount || 0);
                return acc;
              }, {})
            ).map(([method, amount]) => ({ name: method, value: amount }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成每日收款汇总报表失败:', error);
      throw error;
    }
  }

  // 3. 支付报表 (Payment Report)
  async getPaymentReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        payment_method
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (payment_method) {
        whereClause += ' AND o.payment_method = ?';
        params.push(payment_method);
      }

      const sql = `
        SELECT 
          o.payment_method,
          -- 支付金额
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as payment_amount,
          -- 支付笔数
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as payment_count,
          -- 退款金额
          SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END) as refund_amount,
          -- 退款笔数
          COUNT(CASE WHEN o.status = 'refunded' THEN 1 END) as refund_count,
          -- 手续费
          SUM(CASE 
            WHEN o.payment_method IN ('alipay', 'wechat') AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.006 
            WHEN o.payment_method = 'card' AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.008 
            ELSE 0 
          END) as service_fee,
          -- 净入账
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END) - 
          SUM(CASE 
            WHEN o.payment_method IN ('alipay', 'wechat') AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.006 
            WHEN o.payment_method = 'card' AND o.payment_status = 'completed' 
            THEN o.total_amount * 0.008 
            ELSE 0 
          END) as net_amount,
          -- 第三方对账差异 (模拟数据)
          (RAND() - 0.5) * 10 as reconciliation_variance,
          -- 平均客单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as avg_transaction_amount
        FROM orders o
        ${whereClause}
        GROUP BY o.payment_method
        ORDER BY payment_amount DESC
      `;

      const data = await db.query(sql, params);

      const summary = {
        total_payment_amount: data.reduce((sum, row) => sum + parseFloat(row.payment_amount || 0), 0),
        total_payment_count: data.reduce((sum, row) => sum + parseInt(row.payment_count || 0), 0),
        total_refund_amount: data.reduce((sum, row) => sum + parseFloat(row.refund_amount || 0), 0),
        total_service_fee: data.reduce((sum, row) => sum + parseFloat(row.service_fee || 0), 0),
        total_net_amount: data.reduce((sum, row) => sum + parseFloat(row.net_amount || 0), 0),
        total_variance: data.reduce((sum, row) => sum + Math.abs(parseFloat(row.reconciliation_variance || 0)), 0)
      };

      // 异常告警
      const alerts = data.filter(row => Math.abs(row.reconciliation_variance) > 20).map(row => ({
        type: 'payment_variance',
        message: `${row.payment_method}支付渠道对账差异: ${row.reconciliation_variance.toFixed(2)}元`,
        severity: Math.abs(row.reconciliation_variance) > 50 ? 'high' : 'medium',
        data: row
      }));

      return {
        data,
        summary,
        alerts,
        charts: [
          {
            type: 'pie',
            title: '支付方式占比',
            data: data.map(row => ({
              name: row.payment_method,
              value: parseFloat(row.payment_amount || 0)
            }))
          },
          {
            type: 'bar',
            title: '各支付方式手续费',
            data: data.map(row => ({
              x: row.payment_method,
              y: parseFloat(row.service_fee || 0)
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成支付报表失败:', error);
      throw error;
    }
  }

  // 4. 利润分析报表 (Profit Analysis Report)
  async getProfitAnalysisReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        category_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (category_id) {
        whereClause += ' AND mi.category_id = ?';
        params.push(category_id);
      }

      const sql = `
        SELECT 
          DATE(o.created_at) as date,
          COALESCE(mc.name, '未分类') as category_name,
          -- 净销售额 (扣除退款)
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END) as net_sales,
          -- 成本 (假设为销售额的40%)
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * 0.4 as cost_of_goods,
          -- 毛利
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * 0.6 as gross_profit,
          -- 毛利率
          60 as gross_profit_rate,
          -- 人工成本分摊 (假设为销售额的15%)
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * 0.15 as labor_cost,
          -- 房租分摊 (假设为销售额的8%)
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * 0.08 as rent_cost,
          -- 水电分摊 (假设为销售额的3%)
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * 0.03 as utility_cost,
          -- 营销分摊 (假设为销售额的5%)
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * 0.05 as marketing_cost,
          -- 净利润
          (SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END)) * (0.6 - 0.15 - 0.08 - 0.03 - 0.05) as net_profit,
          -- 净利润率
          (0.6 - 0.15 - 0.08 - 0.03 - 0.05) * 100 as net_profit_rate
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        LEFT JOIN menu_items mi ON oi.menu_item_id = mi.id
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        ${whereClause}
        GROUP BY DATE(o.created_at), mc.id, mc.name
        ORDER BY date DESC, net_sales DESC
      `;

      const data = await db.query(sql, params);

      // 按日期汇总
      const dailySummary = {};
      data.forEach(row => {
        if (!dailySummary[row.date]) {
          dailySummary[row.date] = {
            date: row.date,
            net_sales: 0,
            cost_of_goods: 0,
            gross_profit: 0,
            labor_cost: 0,
            rent_cost: 0,
            utility_cost: 0,
            marketing_cost: 0,
            net_profit: 0
          };
        }
        
        dailySummary[row.date].net_sales += parseFloat(row.net_sales || 0);
        dailySummary[row.date].cost_of_goods += parseFloat(row.cost_of_goods || 0);
        dailySummary[row.date].gross_profit += parseFloat(row.gross_profit || 0);
        dailySummary[row.date].labor_cost += parseFloat(row.labor_cost || 0);
        dailySummary[row.date].rent_cost += parseFloat(row.rent_cost || 0);
        dailySummary[row.date].utility_cost += parseFloat(row.utility_cost || 0);
        dailySummary[row.date].marketing_cost += parseFloat(row.marketing_cost || 0);
        dailySummary[row.date].net_profit += parseFloat(row.net_profit || 0);
      });

      const summary = {
        total_net_sales: data.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0),
        total_cost_of_goods: data.reduce((sum, row) => sum + parseFloat(row.cost_of_goods || 0), 0),
        total_gross_profit: data.reduce((sum, row) => sum + parseFloat(row.gross_profit || 0), 0),
        total_operating_cost: data.reduce((sum, row) => 
          sum + parseFloat(row.labor_cost || 0) + parseFloat(row.rent_cost || 0) + 
          parseFloat(row.utility_cost || 0) + parseFloat(row.marketing_cost || 0), 0),
        total_net_profit: data.reduce((sum, row) => sum + parseFloat(row.net_profit || 0), 0)
      };

      if (summary.total_net_sales > 0) {
        summary.gross_profit_rate = (summary.total_gross_profit / summary.total_net_sales * 100).toFixed(2);
        summary.net_profit_rate = (summary.total_net_profit / summary.total_net_sales * 100).toFixed(2);
      }

      // 异常告警
      const alerts = [];
      data.forEach(row => {
        if (parseFloat(row.net_profit || 0) < 0) {
          alerts.push({
            type: 'negative_profit',
            message: `${row.date} ${row.category_name}分类出现负毛利: ${row.net_profit.toFixed(2)}元`,
            severity: 'high',
            data: row
          });
        }
      });

      return {
        data,
        daily_summary: Object.values(dailySummary),
        summary,
        alerts,
        charts: [
          {
            type: 'line',
            title: '利润趋势',
            data: Object.values(dailySummary).map(day => ({
              x: day.date,
              y: day.net_profit
            }))
          },
          {
            type: 'waterfall',
            title: '利润结构分析',
            data: [
              { name: '净销售', value: summary.total_net_sales },
              { name: '商品成本', value: -summary.total_cost_of_goods },
              { name: '人工成本', value: -data.reduce((sum, row) => sum + parseFloat(row.labor_cost || 0), 0) },
              { name: '房租成本', value: -data.reduce((sum, row) => sum + parseFloat(row.rent_cost || 0), 0) },
              { name: '水电成本', value: -data.reduce((sum, row) => sum + parseFloat(row.utility_cost || 0), 0) },
              { name: '营销成本', value: -data.reduce((sum, row) => sum + parseFloat(row.marketing_cost || 0), 0) },
              { name: '净利润', value: summary.total_net_profit }
            ]
          }
        ]
      };
    } catch (error) {
      logger.error('生成利润分析报表失败:', error);
      throw error;
    }
  }
}

module.exports = new FinancialReportController();